﻿using Dapper;
using System;
using System.Linq;
using SilenthinkContract;
using System.Collections.Generic;
using SilenthinkContract.Domain;

namespace SilenthinkService
{
    public class BaseService : ServiceBase, IBaseService
    {
        #region Location

        public IList<Location> GetLocations()
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Base.Table.Location)
                .OrderBy("Id ASC");

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<Location>(connection, template).ToList();
            }
        }

        #endregion

        #region HelpQuestion

        public bool CreateHelpQuestion(HelpQuestion o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.HelpQuestion)
                .Field(
                    "([Title],[Content],[Category],[DisplayOrder],[ShowOnMenu],[CreatedBy],[CreatedOn])VALUES(@Title,@Content,@Category,@DisplayOrder,@ShowOnMenu,@CreatedBy,@CreatedOn)")
                .AddParameters(
                    new {o.Title, o.Content, o.Category, o.DisplayOrder, o.ShowOnMenu, o.CreatedBy, o.CreatedOn});

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateHelpQuestion(HelpQuestion o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.HelpQuestion)
                .Field("[Title]=@Title", new {o.Title})
                .Field("[Content]=@Content", new {o.Content})
                .Field("[Category]=@Category", new {o.Category})
                .Field("[DisplayOrder]=@DisplayOrder", new {o.DisplayOrder})
                .Field("[ShowOnMenu]=@ShowOnMenu", new {o.ShowOnMenu})
                .Where("[Id] = @Id", new {o.Id});

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteHelpQuestion(int id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.HelpQuestion)
                .Where("[Id] = @Id", new {id});

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public HelpQuestion GetHelpQuestion(int id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Base.Table.HelpQuestion)
                .Where("[Id] = @Id", new {id});

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<HelpQuestion>(connection, template).FirstOrDefault();
            }
        }

        public IList<HelpQuestion> GetAllHelpQuestion()
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Base.Table.HelpQuestion);

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<HelpQuestion>(connection, template).ToList();
            }
        }

        public PageList<HelpQuestion> GetPagedHelpQuestion(int index = 1, int size = 20, string title = null,
                                                           byte? category = null, bool? showOnMenu = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Base.Table.HelpQuestion)
                .OrderBy("Category,DisplayOrder")
                .AddParameters(new {begin = (index - 1)*size + 1, end = index*size});
            if (!string.IsNullOrEmpty(title))
            {
                builder.Where("[Title] LIKE @Title", new {title = title + "%"});
            }
            if (category.HasValue && category.Value > 0)
            {
                builder.Where("[Category] = @Category", new {category});
            }
            if (showOnMenu.HasValue)
            {
                builder.Where("[ShowOnMenu]=@ShowOnMenu", new {showOnMenu});
            }

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return PageList<HelpQuestion>(connection, template);
            }
        }

        #endregion

        #region EmailAccount

        public bool CreateEmailAccount(EmailAccount o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.EmailAccount)
                .Field(
                    "([Category],[Email],[DisplayName],[Host],[Port],[Username],[Password],[EnableSsl],[UseDefaultCredentials],[Status],[CreatedBy],[CreatedOn])VALUES(@Category,@Email,@DisplayName,@Host,@Port,@Username,@Password,@EnableSsl,@UseDefaultCredentials,@Status,@CreatedBy,@CreatedOn)")
                .AddParameters(
                    new
                        {
                            o.Category,
                            o.Email,
                            o.DisplayName,
                            o.Host,
                            o.Port,
                            o.Username,
                            o.Password,
                            o.EnableSsl,
                            o.UseDefaultCredentials,
                            o.Status,
                            o.CreatedBy,
                            o.CreatedOn
                        });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateEmailAccount(EmailAccount o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.EmailAccount)
                .Field("[Category]=@Category", new {o.Category})
                .Field("[Email]=@Email", new {o.Email})
                .Field("[DisplayName]=@DisplayName", new {o.DisplayName})
                .Field("[Host]=@Host", new {o.Host})
                .Field("[Port]=@Port", new {o.Port})
                .Field("[Username]=@Username", new {o.Username})
                .Field("[Password]=@Password", new {o.Password})
                .Field("[EnableSsl]=@EnableSsl", new {o.EnableSsl})
                .Field("[UseDefaultCredentials]=@UseDefaultCredentials", new {o.UseDefaultCredentials})
                .Field("[Status]=@Status", new {o.Status})
                .Where("[Id] = @Id", new {o.Id}).Where("[Version]=@Version", new {o.Version});

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteEmailAccount(int id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.EmailAccount)
                .Where("[Id] = @Id", new {id});

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public EmailAccount GetEmailAccount(int id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Base.Table.EmailAccount)
                .Where("[Id] = @Id", new {id});

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<EmailAccount>(connection, template).FirstOrDefault();
            }
        }

        public IList<EmailAccount> GetAllEmailAccount()
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Base.Table.EmailAccount);

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<EmailAccount>(connection, template).ToList();
            }
        }

        public PageList<EmailAccount> GetPagedEmailAccount(int index = 1, int size = 20)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Base.Table.EmailAccount)
                .OrderBy("id desc")
                .AddParameters(new {begin = (index - 1)*size + 1, end = index*size});

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return PageList<EmailAccount>(connection, template);
            }
        }

        #endregion

        #region EmailTemplate

        public bool CreateEmailTemplate(EmailTemplate o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.EmailTemplate)
                .Field(
                    "([Name],[BccEmailAddresses],[Subject],[Body],[Description],[CreatedBy],[CreatedOn])VALUES(@Name,@BccEmailAddresses,@Subject,@Body,@Description,@CreatedBy,@CreatedOn)")
                .AddParameters(
                    new {o.Name, o.BccEmailAddresses, o.Subject, o.Body, o.Description, o.CreatedBy, o.CreatedOn});

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateEmailTemplate(EmailTemplate o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.EmailTemplate)
                .Field("[Name]=@Name", new {o.Name})
                .Field("[BccEmailAddresses]=@BccEmailAddresses", new {o.BccEmailAddresses})
                .Field("[Subject]=@Subject", new {o.Subject})
                .Field("[Body]=@Body", new {o.Body})
                .Field("[Description]=@Description", new {o.Description})
                .Where("[Version]=@Version", new {o.Version})
                .Where("[Id] = @Id", new {o.Id});

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteEmailTemplate(int id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Base.Table.EmailTemplate)
                .Where("[Id] = @Id", new {id});

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Execute(connection, template) == 1;
            }
        }

        public EmailTemplate GetEmailTemplate(int id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Base.Table.EmailTemplate)
                .Where("[Id] = @Id", new {id});

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<EmailTemplate>(connection, template).FirstOrDefault();
            }
        }

        public IList<EmailTemplate> GetAllEmailTemplate()
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Base.Table.EmailTemplate);
            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return Query<EmailTemplate>(connection, template).ToList();
            }
        }

        public PageList<EmailTemplate> GetPagedEmailTemplate(int index = 1, int size = 20)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Base.Table.EmailTemplate)
                .OrderBy("id desc")
                .AddParameters(new {begin = (index - 1)*size + 1, end = index*size});
            /*
            .Where("");
            */

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Base))
            {
                return PageList<EmailTemplate>(connection, template);
            }
        }

        #endregion

        #region Implementation of IService

        public int GetCachedSQLCount()
        {
            return SqlMapper.GetCachedSQLCount();
        }

        public List<Tuple<string, string, int>> GetCachedSQL()
        {
            return SqlMapper.GetCachedSQL().ToList();
        }

        public void PurgeQueryCache()
        {
            SqlMapper.PurgeQueryCache();
        }

        #endregion

    }
}